﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using Modellayer;

namespace Dblayer
{
    public class DbShowRoom
    {
        SqlCommand cmd = null;

        public DbShowRoom()
        {
 
        }

        public void createShowRoom(ShowRoom sh)
        {
            try
            {
                Connection.dbconn.Open();

                string create = @"insert into ShowRoom(ShowRoomNumber, NumberOfseats, ShowRoomDescription)
                    Values ('" + sh.showRoomNumber + "'," + "'" + sh.numberOfSeats + "'," + "'" + sh.showRoomDescription + "')";
                cmd = Connection.GetDBCommand(create);
                cmd.ExecuteReader();
            }
            catch (Exception e)
            {
                Console.WriteLine("{0}", e.Message);
            }
            finally
            {
                if (Connection.dbconn != null)
                {
                    Connection.dbconn.Close();
                }
            }
        }

        public ShowRoom findShowRoom(int showRoomNumber)
        {
            string sql = @"SELECT * FROM ShowRoom WHERE ShowRoomNumber = '" + showRoomNumber + "'";
            return buildshowRoom(sql);
        }

        private ShowRoom buildshowRoom(string showRoom)
        {
            System.Data.IDataReader dbReader;
            ShowRoom s = null;

            try
            {

                cmd = Connection.GetDBCommand(showRoom);
                dbReader = cmd.ExecuteReader();
                while(dbReader.Read())
                {

                    s = new ShowRoom();
                    s.showRoomNumber = int.Parse(dbReader["ShowRoomNumber"].ToString());
                    s.numberOfSeats = int.Parse(dbReader["NumberOfSeats"].ToString());
                    s.showRoomDescription = dbReader["ShowRoomDescription"].ToString();

                }
                Connection.Close();
            }
            catch (Exception e)
            {
                Console.WriteLine("Could not find Specified ShowRoom! " + e.Message);
                s = null; 
            }
            return s;
        }

        public void updateShowRoom(ShowRoom sh)
        {
            try
            {
                string update = @"UPDATE ShowRoom SET " + "NumberOfSeats = '" + sh.numberOfSeats + "'," 
                    + " ShowRoomDescription ='" + sh.showRoomDescription + "'," + "WHERE ShowRoomNumber = '" + sh.showRoomNumber + "'";
                cmd = Connection.GetDBCommand(update);
                cmd.ExecuteReader();
            }
            catch (Exception e)
            {
                Console.WriteLine("{0}", e.Message);
            }
            finally
            {
                if (Connection.dbconn != null)
                {
                    Connection.dbconn.Close();
                }
            }
        }

        public List<ShowRoom> findAllShowRooms()
        {
            string sql = @"SELECT * FROM ShowRoom";
            return buildAllShowRoom(sql);
        }

        private List<ShowRoom> buildAllShowRoom(string showRoom)
        {
            List<ShowRoom> showR = new List<ShowRoom>();
            System.Data.IDataReader dbReader;
            ShowRoom s = null;

            try
            {

                cmd = Connection.GetDBCommand(showRoom);
                dbReader = cmd.ExecuteReader();
                while (dbReader.Read())
                {

                    s = new ShowRoom();
                    s.showRoomNumber = int.Parse(dbReader["ShowRoomNumber"].ToString());
                    s.numberOfSeats = int.Parse(dbReader["NumberOfSeats"].ToString());
                    s.showRoomDescription = dbReader["ShowRoomDescription"].ToString();
                    showR.Add(s);
                }
                Connection.Close();
                dbReader.Close();
            }
            catch (Exception e)
            {
                Console.WriteLine("Could not find Specified ShowRoom! " + e.Message);
                s = null;
            }
            return showR;
        }

    }
}
